package dao;

import utils.connect.ConnectSqlDaoImpl;
import utils.string.sql.T_Equipment;
import utils.string.sql.T_Using;
import utils.string.sql.T_User;
import vo.TableUsing;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

public class TableUsingDaoImpl implements TableUsingDao {
    ConnectSqlDaoImpl connectSqlDao = ConnectSqlDaoImpl.getInStance();
    @Override
    public HashMap selectAll() {
        HashMap tableUsings = new HashMap();
        try {
            connectSqlDao.initConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection connection = connectSqlDao.getConnection();
        String sql = "select "+
                T_Using.USING_ID+","+
                T_Using.USING_NUM+","+
                T_Using.USING_REASON+","+
                T_Using.USING_DATE+","+
                T_User.USER_NAME +","+
                T_Equipment.EQUIP_NAME +","+
                T_Equipment.TABLE_NAME+"."+T_Equipment.EQUIP_ID+","+
                T_Using.TABLE_NAME+"."+T_Using.USER_ID+" from " +
                T_Using.TABLE_NAME+"," +
                T_Equipment.TABLE_NAME+","+
                T_User.TABLE_NAME+" where "+
                T_Using.TABLE_NAME+"."+T_Using.USER_ID+" = "+T_User.TABLE_NAME+"."+T_User.USER_ID+" AND "+
                T_Using.TABLE_NAME+"."+T_Using.EQUIP_ID+" = "+T_Equipment.TABLE_NAME+"."+T_Equipment.EQUIP_ID;
        try {
            PreparedStatement preparedStatement =connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            System.out.println("sql");
            while(resultSet.next()){
                TableUsing tableUsing = new TableUsing();
                tableUsing.setEquip_id(resultSet.getInt(T_Using.EQUIP_ID));
                tableUsing.setEquip_name(resultSet.getString(T_Equipment.EQUIP_NAME));
                tableUsing.setUsing_id(resultSet.getInt(T_Using.USING_ID));
                tableUsing.setUser_id(resultSet.getInt(T_User.USER_ID));
                tableUsing.setUsing_num(resultSet.getInt(T_Using.USING_NUM));
                tableUsing.setUsing_reason(resultSet.getString(T_Using.USING_REASON));
                tableUsing.setUser_name(resultSet.getString(T_User.USER_NAME));
                tableUsing.setUsing_date(resultSet.getDate(T_Using.USING_DATE));
                tableUsings.put(tableUsing.getUsing_id(),tableUsing);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connectSqlDao.closeConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableUsings;
    }

    @Override
    public HashMap selectByUserId(int id) {
        HashMap tableUsings = new HashMap();
        try {
            connectSqlDao.initConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection connection = connectSqlDao.getConnection();
        String sql = "select "+
                T_Using.USING_ID+","+
                T_Using.USING_NUM+","+
                T_Using.USING_REASON+","+
                T_Using.USING_DATE+","+
                T_User.USER_NAME +","+
                T_Equipment.EQUIP_NAME +","+
                T_Equipment.TABLE_NAME+"."+T_Equipment.EQUIP_ID+","+
                T_Using.TABLE_NAME+"."+T_Using.USER_ID+" from " +
                T_Using.TABLE_NAME+"," +
                T_Equipment.TABLE_NAME+","+
                T_User.TABLE_NAME+" where "+
                T_Using.TABLE_NAME+"."+T_Using.USER_ID+" = "+T_User.TABLE_NAME+"."+T_User.USER_ID+" AND "+
                T_Using.TABLE_NAME+"."+T_Using.EQUIP_ID+" = "+T_Equipment.TABLE_NAME+"."+T_Equipment.EQUIP_ID
                +" AND "+T_Using.TABLE_NAME+"."+T_Using.USER_ID + " = ?";;
        try {
            PreparedStatement preparedStatement =connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            ResultSet resultSet = preparedStatement.executeQuery();
            System.out.println("sql");
            while(resultSet.next()){
                TableUsing tableUsing = new TableUsing();
                tableUsing.setEquip_id(resultSet.getInt(T_Using.EQUIP_ID));
                tableUsing.setEquip_name(resultSet.getString(T_Equipment.EQUIP_NAME));
                tableUsing.setUsing_id(resultSet.getInt(T_Using.USING_ID));
                tableUsing.setUser_id(resultSet.getInt(T_User.USER_ID));
                tableUsing.setUsing_num(resultSet.getInt(T_Using.USING_NUM));
                tableUsing.setUsing_reason(resultSet.getString(T_Using.USING_REASON));
                tableUsing.setUser_name(resultSet.getString(T_User.USER_NAME));
                tableUsing.setUsing_date(resultSet.getDate(T_Using.USING_DATE));
                tableUsings.put(tableUsing.getUsing_id(),tableUsing);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connectSqlDao.closeConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableUsings;
    }
}
